import mysql.connector

class MySQLDB:
    def __init__(self, host, user, password, database):
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.connection = None
        self.cursor = None

    def connect(self):
        self.connection = mysql.connector.connect(
            host=self.host,
            user=self.user,
            password=self.password,
            database=self.database
        )
        self.cursor = self.connection.cursor()

    def close(self):
        if self.cursor:
            self.cursor.close()
        if self.connection:
            self.connection.close()

    def execute(self, query, params=None):
        if not self.cursor:
            raise Exception("Cursor is not initialized. Please connect to the database first.")
        if params:
            self.cursor.execute(query, params)
        else:
            self.cursor.execute(query)
        return self.cursor.fetchall()

    def insert(self, table, data_dict):
        keys = ', '.join(data_dict.keys())
        values = ', '.join(['%s'] * len(data_dict))
        query = f"INSERT INTO {table} ({keys}) VALUES ({values})"
        self.cursor.execute(query, tuple(data_dict.values()))
        # 打印完整sql
        print(self.cursor.statement)
        self.connection.commit()

    def update(self, table, data_dict, where_clause):
        sets = ', '.join([f"{k}=%s" for k in data_dict.keys()])
        query = f"UPDATE {table} SET {sets} WHERE {where_clause}"
        params = list(data_dict.values())
        print(query)
        print(params)
         # 打印完整sql
        print(query % tuple(params))
        print(self.cursor.statement)
        self.cursor.execute(query, params)
        self.connection.commit()

    def delete(self, table, where_clause):
        query = f"DELETE FROM {table} WHERE {where_clause}"
         # 打印完整sql
        print(query)
         # 打印完整sql
        self.cursor.execute(query)
        # print(self.cursor.statement)
        self.connection.commit()


if __name__ == "__main__":
    db = MySQLDB("10.200.32.179", "root", "111111", "f21010235")
    db.connect()
    
    # 查询 images_qa 表中的所有数据
    print(db.execute("SELECT * FROM images_qa"))
    
    # 向 images_qa 表中插入一条新记录
    # 注意：由于 id 字段未设置为自动递增，如果需要，您可能需要手动提供一个唯一的 id 值
    # 这里我们假设 id 是自动处理的，例如通过触发器或其他机制，或者您可以在插入前获取最大的 id 并加 1
    db.insert("images_qa", {"image_path": "path/to/image.jpg", "question": "What is in the image?", "answer": "A cat."})
    
    # 更新 images_qa 表中的一条记录
    # 假设我们要更新 id 为 2 的记录的 question 字段
    db.update("images_qa", {"question": "Updated question.","answer":"新的答案"}, "id=2")
    
    # 删除 images_qa 表中的一条记录
    # 假设我们要删除 id 为 3 的记录
    db.delete("images_qa", "id=6")
    
    db.close()